-- add column
ALTER TABLE phase ADD COLUMN remark character varying;
ALTER TABLE phase ADD COLUMN "index" integer;

-- procedure
CREATE OR REPLACE FUNCTION sp_phase_insert(
	_name	character varying(255) DEFAULT null,
	_code	character varying(20) DEFAULT null,
	_description	character varying DEFAULT null,
	_id_project	integer DEFAULT null, 
	_status	integer DEFAULT null,
	_plan_start_date	timestamp without time zone DEFAULT null,
	_plan_end_date	timestamp without time zone DEFAULT null,
	_real_start_date	timestamp without time zone DEFAULT null,
	_real_end_date	timestamp without time zone DEFAULT null,
	_creator	integer DEFAULT null,
	_remark	character varying DEFAULT null,
	_index	integer DEFAULT null
)RETURNS int AS $$
BEGIN
	INSERT INTO phase(name,code,description,id_project,status,plan_start_date,plan_end_date,real_start_date,real_end_date,creator,updator,deletion_time,remark,index)
	VALUES(_name,_code,_description,_id_project,_status,_plan_start_date,_plan_end_date,_real_start_date,_real_end_date,_creator,_creator,null,_remark,_index);
RETURN currval('phase_id_seq'::regclass);
END;
$$ LANGUAGE 'plpgsql';


CREATE OR REPLACE FUNCTION sp_phase_select(_id	integer)
RETURNS SETOF phase
AS $$
BEGIN
	RETURN QUERY SELECT * FROM phase WHERE id = _id;
END;
$$ LANGUAGE 'plpgsql';


CREATE OR REPLACE FUNCTION sp_phase_selectList (
	_limit	integer,
	_offset integer,
	_id	integer DEFAULT null,
	_name	character varying(255) DEFAULT null,
	_code	character varying(20) DEFAULT null,
	_description	character varying DEFAULT null,
	_id_project	integer DEFAULT null,
	_status	integer DEFAULT null,
	_plan_start_dateFrom	timestamp without time zone DEFAULT null,
	_plan_start_dateTo	timestamp without time zone DEFAULT null,
	_plan_end_dateFrom	timestamp without time zone DEFAULT null,
	_plan_end_dateTo	timestamp without time zone DEFAULT null,
	_real_start_dateFrom	timestamp without time zone DEFAULT null,
	_real_start_dateTo	timestamp without time zone DEFAULT null,
	_real_end_dateFrom	timestamp without time zone DEFAULT null,
	_real_end_dateTo	timestamp without time zone DEFAULT null,
	_createdFrom	timestamp without time zone DEFAULT null,
	_createdTo	timestamp without time zone DEFAULT null,
	_creator	integer DEFAULT null,
	_updatedFrom	timestamp without time zone DEFAULT null,
	_updatedTo	timestamp without time zone DEFAULT null,
	_updator	integer DEFAULT null,
	_deleted	boolean DEFAULT null,
	_deletion_timeFrom	timestamp without time zone DEFAULT null,
	_deletion_timeTo	timestamp without time zone DEFAULT null,
	_remark	character varying DEFAULT null,
	_index	integer DEFAULT null
)
RETURNS SETOF phase
AS $$
BEGIN
	IF _limit IS NULL THEN
		_limit = 2147483647;
	END IF;
	IF _offset IS NULL THEN
		_offset = 0;
	END IF;
RETURN QUERY SELECT * FROM phase WHERE
	 	(_id is null OR id = _id)	
	AND (_name is null OR name = _name)	
	AND (_code is null OR code = _code)	
	AND (_description is null OR description = _description)	
	AND (_id_project is null OR id_project = _id_project)	
	AND (_status is null OR status = _status)	
	AND (_plan_start_dateFrom is null OR plan_start_date >= _plan_start_dateFrom)
	AND	(_plan_start_dateTo is null OR plan_start_date <= _plan_start_dateTo)	
	AND (_plan_end_dateFrom is null OR plan_end_date >= _plan_end_dateFrom)
	AND	(_plan_end_dateTo is null OR plan_end_date <= _plan_end_dateTo)	
	AND (_real_start_dateFrom is null OR real_start_date >= _real_start_dateFrom)
	AND	(_real_start_dateTo is null OR real_start_date <= _real_start_dateTo)	
	AND (_real_end_dateFrom is null OR real_end_date >= _real_end_dateFrom)
	AND	(_real_end_dateTo is null OR real_end_date <= _real_end_dateTo)	
	AND (_createdFrom is null OR created >= _createdFrom)
	AND	(_createdTo is null OR created <= _createdTo)	
	AND (_creator is null OR creator = _creator)	
	AND (_updatedFrom is null OR updated >= _updatedFrom)
	AND	(_updatedTo is null OR updated <= _updatedTo)	
	AND (_updator is null OR updator = _updator)	
	AND (_deleted is null OR deleted = _deleted)	
	AND (_deletion_timeFrom is null OR deletion_time >= _deletion_timeFrom)
	AND	(_deletion_timeTo is null OR deletion_time <= _deletion_timeTo)	
	AND (_remark is null OR remark = _remark)	
	AND (_index is null OR index = _index)	ORDER BY updated DESC  LIMIT _limit OFFSET _offset;
END;
$$ LANGUAGE 'plpgsql';


CREATE OR REPLACE FUNCTION sp_phase_count (
	_id	integer DEFAULT null,
	_name	character varying(255) DEFAULT null,
	_code	character varying(20) DEFAULT null,
	_description	character varying DEFAULT null,
	_id_project	integer DEFAULT null,
	_status	integer DEFAULT null,
	_plan_start_dateFrom	timestamp without time zone DEFAULT null,
	_plan_start_dateTo	timestamp without time zone DEFAULT null,
	_plan_end_dateFrom	timestamp without time zone DEFAULT null,
	_plan_end_dateTo	timestamp without time zone DEFAULT null,
	_real_start_dateFrom	timestamp without time zone DEFAULT null,
	_real_start_dateTo	timestamp without time zone DEFAULT null,
	_real_end_dateFrom	timestamp without time zone DEFAULT null,
	_real_end_dateTo	timestamp without time zone DEFAULT null,
	_createdFrom	timestamp without time zone DEFAULT null,
	_createdTo	timestamp without time zone DEFAULT null,
	_creator	integer DEFAULT null,
	_updatedFrom	timestamp without time zone DEFAULT null,
	_updatedTo	timestamp without time zone DEFAULT null,
	_updator	integer DEFAULT null,
	_deleted	boolean DEFAULT null,
	_deletion_timeFrom	timestamp without time zone DEFAULT null,
	_deletion_timeTo	timestamp without time zone DEFAULT null,
	_remark	character varying DEFAULT null,
	_index	integer DEFAULT null
)
RETURNS int AS $$
BEGIN
	RETURN COUNT(*) FROM phase WHERE
	 	(_id is null OR id = _id)	
	AND (_name is null OR name = _name)	
	AND (_code is null OR code = _code)	
	AND (_description is null OR description = _description)	
	AND (_id_project is null OR id_project = _id_project)	
	AND (_status is null OR status = _status)	
	AND (_plan_start_dateFrom is null OR plan_start_date >= _plan_start_dateFrom)
	AND	(_plan_start_dateTo is null OR plan_start_date <= _plan_start_dateTo)	
	AND (_plan_end_dateFrom is null OR plan_end_date >= _plan_end_dateFrom)
	AND	(_plan_end_dateTo is null OR plan_end_date <= _plan_end_dateTo)	
	AND (_real_start_dateFrom is null OR real_start_date >= _real_start_dateFrom)
	AND	(_real_start_dateTo is null OR real_start_date <= _real_start_dateTo)	
	AND (_real_end_dateFrom is null OR real_end_date >= _real_end_dateFrom)
	AND	(_real_end_dateTo is null OR real_end_date <= _real_end_dateTo)	
	AND (_createdFrom is null OR created >= _createdFrom)
	AND	(_createdTo is null OR created <= _createdTo)	
	AND (_creator is null OR creator = _creator)	
	AND (_updatedFrom is null OR updated >= _updatedFrom)
	AND	(_updatedTo is null OR updated <= _updatedTo)	
	AND (_updator is null OR updator = _updator)	
	AND (_deleted is null OR deleted = _deleted)	
	AND (_deletion_timeFrom is null OR deletion_time >= _deletion_timeFrom)
	AND	(_deletion_timeTo is null OR deletion_time <= _deletion_timeTo)	
	AND (_remark is null OR remark = _remark)	
	AND (_index is null OR index = _index);
END;
$$ LANGUAGE plpgsql;


CREATE OR REPLACE FUNCTION sp_phase_delete(_id integer)
	RETURNS INT as
$$
BEGIN
		DELETE FROM phase
 		WHERE "id" = _id;
		RETURN 0;
END;
$$ LANGUAGE 'plpgsql';


CREATE OR REPLACE FUNCTION sp_phase_update 
(	_id integer,
	_name character varying(255),
	_code character varying(20),
	_description character varying,
	_id_project integer,
	_status integer,
	_plan_start_date timestamp without time zone,
	_plan_end_date timestamp without time zone,
	_real_start_date timestamp without time zone,
	_real_end_date timestamp without time zone,
	_updator integer,
	_deleted boolean,
	_deletion_time timestamp without time zone,
	_remark character varying,
	_index integer  ) 
RETURNS INT AS 
$$
BEGIN 
	UPDATE phase 
	SET 		name = _name,
		code = _code,
		description = _description,
		id_project = _id_project,
		status = _status,
		plan_start_date = _plan_start_date,
		plan_end_date = _plan_end_date,
		real_start_date = _real_start_date,
		real_end_date = _real_end_date,
		updated = now(),
		updator = _updator,
		deleted = _deleted,
		deletion_time = _deletion_time,
		remark = _remark,
		index = _index
 	WHERE id = _id;
	RETURN 0;
END; 
$$ LANGUAGE plpgsql;
